unit daoru_1form;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, BCButton, BCLabel, Forms, intf, nkTitleBar,
  dateutils, Controls, Graphics, Dialogs, ExtCtrls, StdCtrls, ComCtrls, Spin,
  fpstypes, fpspreadsheet, fpspreadsheetctrls, fpspreadsheetgrid, fpsActions,
  fpsAllFormats, fpsutils
  ;

type

  { TfrmDaoru_1 }

  TfrmDaoru_1 = class(TForm)
    Bar1: TProgressBar;
    BCButton1: TBCButton;
    BCLabel1: TBCLabel;
    Button1: TButton;
    Button2: TButton;
    CheckBox1: TCheckBox;
    Image2: TImage;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    ls: TListView;
    Memo1: TMemo;
    Memo2: TMemo;
    Memo3: TMemo;
    nkTitleBar1: TnkTitleBar;
    OpenDialog1: TOpenDialog;
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    Panel5: TPanel;
    Panel6: TPanel;
    Panel7: TPanel;
    Panel8: TPanel;
    SpinEdit1: TSpinEdit;
    SpinEdit2: TSpinEdit;
    procedure BCButton1Click(Sender: TObject);
    procedure BCLabel1Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Image2MouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure Image2MouseMove(Sender: TObject; Shift: TShiftState; X, Y: Integer
      );
    procedure Image2MouseUp(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure lsChange(Sender: TObject; Item: TListItem; Change: TItemChange);
    procedure lsSelectItem(Sender: TObject; Item: TListItem; Selected: Boolean);
  private
    FKind: TnkImportKind;
    Moving:boolean;
    XPos,YPos:integer;

    procedure SetKind(AValue: TnkImportKind);
  public

    procedure MyReadCellDataHandler(Sender: TObject; ARow, ACol: Cardinal;const ADataCell: PCell);
    function CheckFileExists(AFilename:string):boolean;//检查文件是否已经存在列表中
    procedure Log(AText:string;AError:boolean=False);//插入日志
    procedure Daoru_Huabo(Sender: TObject);//导入职工医疗划拨数据表的过程
    procedure Daoru_Siwang(Sender:TObject);//导入死亡人口数据表的过程
    procedure Daoru_Danwei(Sender:TObject);//导入单位信息表
    procedure Daoru_Zhuxiao(Sender:TObject);//导入企业注销名单
    procedure Daoru_Xuesheng(Sender:TObject);//导入学生居民医疗保险参保登记表
    procedure Daoru_Zhuanchu(Sender:TObject);//导入职工医疗保险转出名单表
    procedure Daoru_Bujiao(Sender:TObject);//导入导入职工医疗保险补缴表
    procedure Daoru_Jumin(sender:TObject);//导入居民医疗保险参保人员名单表
    procedure Daoru_Zhigong(sender:TObject);//导入参保职工名单表
    procedure Daoru_Gaoxueya(sender:TObject);//导入高血压、糖尿病办理登记表
    procedure Daoru_Manxingbing(Sender:TObject);//导入居民慢性病办理名单表
    procedure Daoru_ZhigongManxingbing(sender:TObject);//导入职工慢性病办理名单表
    procedure Daoru_ManbingBianma(Sender:TObject);//导入居民慢性病名称和编码
    procedure Daoru_YiyuanBianma(Sender:TObject);//导入定点医院名称编码级别对应表
    procedure Daoru_Zhigongmanxingbingbianma(sender:TObject);//导入职工慢性病名称和编码表
    property Kind:TnkImportKind read FKind write SetKind default tikHuabo;//带入类型
  end;

var
  frmDaoru_1: TfrmDaoru_1;

  function UnixDateToDateTime(const USec: Longint): TDateTime;

implementation

uses
  datamodule;
{$R *.frm}

{ TfrmDaoru_1 }

function UnixDateToDateTime(const USec: Longint): TDateTime;
const
     UnixStartDate: TDateTime = 25569.0; // 1970/01/01
begin
     Result:= (Usec / 86400) + UnixStartDate;
     Result:= IncHour(Result,8);
end;

procedure TfrmDaoru_1.Button1Click(Sender: TObject);
var
  i:integer;
  mFile:string;
begin
  if OpenDialog1.Execute then
  begin
    for i:=0 to OpenDialog1.Files.Count-1 do
    begin
      mFile:=OpenDialog1.Files.Strings[i];
      //检查文件是否存在
      if CheckFileExists(mFile) then
      begin
        MessageDlg('文件“'+mFile+'”已经存在，不能再次添加。',mtWarning,[mbOK],0);
        Continue;
      end;
      with ls.Items.Add do
      begin
        Caption:=inttostr(SpinEdit1.Value)+'年'+inttostr(SpinEdit2.Value)+'月';
        SubItems.Add(ExtractFilename(mFile));
        SubItems.Add(inttostr(SpinEdit1.Value));
        SubItems.Add(inttostr(SpinEdit2.Value));
        SubItems.Add(mFile);
      end;
    end;
  end;
end;

procedure TfrmDaoru_1.BCLabel1Click(Sender: TObject);
begin
  ModalResult:=mrCancel;
end;

procedure TfrmDaoru_1.BCButton1Click(Sender: TObject);
begin

end;


procedure TfrmDaoru_1.Button2Click(Sender: TObject);
begin
  if ls.Selected=nil then
  begin
    MessageDlg('请选中要移除的文件。',mtWarning,[mbOK],0);
  end
  else
  begin
    ls.Selected.Delete;
  end;
end;

procedure TfrmDaoru_1.FormShow(Sender: TObject);
begin
  //初始化年份
  SpinEdit1.Value:=YearOf(Now);
  SpinEdit2.Value:=MonthOf(IncMonth(Now, -1));
  case FKind of
    tikHuabo:
      begin
        Label1.Caption:='导入职工医疗个人账户划拨表';
        CheckBox1.Checked:=True;
        checkBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Huabo;
      end;
    tikSiwang:
      begin
        Label1.Caption:='导入死亡人口数据表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Siwang;
      end;
    tikDanwei:
      begin
        Label1.Caption:='导入单位信息表';
        CheckBox1.Visible:=True;
        CheckBox1.Checked:=True;
        BCButton1.OnClick:=@Daoru_Danwei;
      end;
    tikZhuxiao:
      begin
        Label1.Caption:='导入企业注销名单';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Zhuxiao;
      end;
    tikXuesheng:
      begin
        Label1.Caption:='导入学生居民医疗保险参保登记表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Xuesheng;
      end;
    tikZhuanchu:
      begin
        Label1.Caption:='导入职工医疗保险转出名单表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Zhuanchu;
      end;
    tikBujiao:
      begin
        Label1.Caption:='导入职工医疗保险补缴表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Bujiao;
      end;
    tikJumin:
      begin
        Label1.Caption:='导入居民医疗保险参保人员名单表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Jumin;
      end;
    tikGaoxueya:
      begin
        Label1.Caption:='导入高血压、糖尿病办理登记表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Gaoxueya;
      end;
    tikManxingbing:
      begin
        Label1.Caption:='导入居民慢性病办理名单表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Manxingbing;
      end;
    tikManbingBianma:
      begin
        Label1.Caption:='导入居民慢性病名称和编码';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_ManbingBianma;
      end;
    tikYiyuanBianma:
      begin
        Label1.Caption:='导入定点医院名称编码级别对应表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_YiyuanBianma;
      end;
    tikZhigong:
      begin
        Label1.Caption:='导入参保职工名单表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Zhigong;
      end;
    tikZhigongmanbingBianma:
      begin
        Label1.Caption:='导入职工慢性病名称和编码';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_Zhigongmanxingbingbianma;
      end;
    tikZhigongmanxingbing:
      begin
        Label1.Caption:='导入职工慢性病办理名单表';
        CheckBox1.Checked:=True;
        CheckBox1.Visible:=True;
        BCButton1.OnClick:=@Daoru_ZhigongManxingbing;
      end;
  end;
end;

procedure TfrmDaoru_1.Image2MouseDown(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer);
begin
  if mbLeft = Button then
  begin
    Self.Moving:=True;
    Self.YPos := Y;
    Self.XPos := X;
  end;
end;

procedure TfrmDaoru_1.Image2MouseMove(Sender: TObject; Shift: TShiftState; X,
  Y: Integer);
begin
  if not (csDesigning in Self.ComponentState) then
  begin
    if Self.Moving then
    begin
      Self.Top:=Self.Top+Y-Self.YPos;
      Self.Left:=Self.Left+X-Self.XPos;
      if Self.Top<0 then
        Self.Top:=0;
    end;
  end;
end;

procedure TfrmDaoru_1.Image2MouseUp(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer);
begin
  Moving:=False;
end;

procedure TfrmDaoru_1.lsChange(Sender: TObject; Item: TListItem;
  Change: TItemChange);
begin
end;

procedure TfrmDaoru_1.lsSelectItem(Sender: TObject; Item: TListItem;
  Selected: Boolean);
begin
  if ls.Selected=nil then
  begin
    Memo2.Text:='';
  end
  else
  begin
    //显示列表中选中的文件信息
    Memo2.Lines.Clear;
    Memo2.Lines.Add('文件名称：'+ls.Selected.SubItems.Strings[0]);
    Memo2.Lines.Add('数据日期：'+ls.Selected.Caption);
    Memo2.Lines.Add('文件路径：'+ExtractFilepath(ls.Selected.SubItems.Strings[3]));
  end;
end;

procedure TfrmDaoru_1.SetKind(AValue: TnkImportKind);
begin
  if FKind=AValue then Exit;
  FKind:=AValue;
end;

procedure TfrmDaoru_1.MyReadCellDataHandler(Sender: TObject; ARow,
  ACol: Cardinal; const ADataCell: PCell);
begin
  Unused(ACol, ADataCell);
  Log('Reading '+ inttostr(AROw));
end;

function TfrmDaoru_1.CheckFileExists(AFilename: string): boolean;
var
  i:integer;
begin
  Result:=False;
  for i:=0 to ls.Items.Count-1 do
  begin
    if lowercase(trim(ls.Items.Item[i].SubItems.Strings[3]))=lowercase(trim(AFilename)) then
    begin
      Result:=True;
      Break;
    end;
  end;
end;

procedure TfrmDaoru_1.Log(AText: string;AError:boolean=False);
begin
  if not AError then
  begin
    Memo1.Lines.Add(AText);
    Memo1.Update;
  end
  else
  begin
    Memo3.Lines.Add(AText);
    Memo3.Update;
  end;
  Application.ProcessMessages;
end;

procedure TfrmDaoru_1.Daoru_Huabo(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0:string;
  m1:string;
  m2:string;
  m3:string;
  m4:string;
  m5:string;
  m6:string;
  m7:string;
  mm7:double;
  m8:string;
  m9:string;
  m10:string;
  m11:string;
  m12:string;
  m13:string;
  m14:string;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的同年同月数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的同年同月数据');
      Dm.DeleteHuabo(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    //try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        //Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;

        for j:=1 to mLastRow do
        begin
          {列0: 社会保障号码
          列1：姓名
          列2：性别
          列3：联系电话
          列4：医疗个人账号
          列5：卡号
          列6：银行账号
          列7：总金额
          列8：单位名称
          列9：单位编号
          列10：社保机构编号
          列11：社保机构名称
          列12：异地标志
          列13：单位性质
          列14：经济类型}
          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);
          m9:=mSheet.ReadAsText(j,9);
          m10:=mSheet.ReadAsText(j,10);
          m11:=mSheet.ReadAsText(j,11);
          m12:=mSheet.ReadAsText(j,12);
          m13:=mSheet.ReadAsText(j,13);
          m14:=mSheet.ReadAsText(j,14);
          if (Trim(m0)='')
          or (Trim(m1)='')
          or (Trim(m2)='')
          //or (Trim(m3)='') 电话可以为空
          or (Trim(m4)='')
          or (Trim(m5)='')
          or (Trim(m6)='')
          or (Trim(m7)='')
          or (Trim(m8)='')
          or (Trim(m9)='')
          or (Trim(m10)='')
          or (Trim(m11)='')
          or (Trim(m12)='')
          or (Trim(m13)='')
          or (Trim(m14)='')
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，如果没有事先删除掉同月份的就需要检查了
          mm7:=0;
          if trim(m7)='' then
            mm7:=0
          else
          begin
            try
              mm7:=strtofloat(trim(m7));
            except
              mm7:=0;
            end;
          end;
          dm.InsertHuabo(SpinEdit1.Value,SpinEdit2.Value,m0,m1,m2,m3,m4,m5,m6,mm7,m8,m9,m10,m11,m12,m13,m14,not CheckBox1.Checked,True);

          mCount:=mCount+1;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;
        end;
        //Dm.conn.Commit;
        //Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    //except
    //  Log('读取文件时发生错误：'+mDispFile,True);
    //end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_1_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Siwang(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0:string;
  mm0:TDate;
  m1:string;
  m2:string;
  m3:string;
  FS1,FS2: TFormatSettings;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的同年同月数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的同年同月数据');
      Dm.DeleteSiwang(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow do
        begin
          {列0: 火化时间
          列1：姓名
          列2：身份证号
          列3：户口所在地}
          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          if (Trim(m0)='')
          or (Trim(m1)='')
          {or (Trim(m2)='') //证件号码和户口所在地可以为空
          or (Trim(m3)='')}
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，如果没有事先删除掉同月份的就需要检查了
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          if trim(m0)='' then
            mm0:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m0)>0 then
            begin
              try
                mm0:=strtodate(trim(m0),FS1);
                dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mm0:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m0)>0 then
            begin
              try
                mm0:=strtodate(trim(m0),FS2);
                dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mm0:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm0:=strtodate(copy(trim(m0),1,4)+'-'+copy(trim(m0),5,2)+'-'+copy(trim(m0),7,2),FS1);
                dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mm0:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;

          mCount:=mCount+1;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;
        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Danwei(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0, m1, m2, m3, m4, m5, m6, m7, m8, m9:string;
  m10, m11:string;
  mm10, mm11:TDate;
  m12, m13, m14, m15, m16, m17:string;
  mm12:double;
  mm13, mm14, mm15, mm16, mm17:integer;
  m18, m19, m20, m21, m22, m23, m24, m25, m26, m27, m28, m29, m30, m31, m32, m33, m34, m35, m36, m37, m38, m39, m40, m41:string;
  FS1,FS2: TFormatSettings;
  mTmp:string;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的相同数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteDanwei(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;

  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow-1 do
        begin
          {c0: 单位编号
           c1: 税务单位编号
           c2: 参保单位名称
           c3: 统一社会信用代码
           c4: 组织机构代码
           c5: 参保单位性质
           c6: 经济类型
           c7: 经济类型明细
           c8: 税务征收标志
           c9: 投保类别
           c10: 最大缴满年月
           c11: 最大应缴年月
           c12: 在职人员缴费基数
           c13: 在职人数
           c14: 退休人数
           c15: 退职人数
           c16: 离休人数
           c17: 中断人数
           c18: 单位专管员姓名
           c19: 单位专管员电话
           c20: 单位地址
           c21: 邮政编码
           c22: 隶属关系
           c23: 行业代码
           c24: 行业风险类别
           c25: 主管部门或总机构
           c26: 法定代表人姓名
           c27: 法定代表人电话
           c28: 法定代表人证件类型
           c29: 法定代表人证件号码
           c30: 工商登记执照种类
           c31: 工商登记执照号码
           c32: 工商登记发照日期
           c33: 工商登记有效期限
           c34: 缴费银行户名
           c35: 缴费银行账号
           c36: 社保登记证号
           c37: 街道编号
           c38: 街道名称
           c39: 经办机构编号
           c40: 经办机构名称
           c41: 参保单位c2名称之后的特殊标记}

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);
          m9:=mSheet.ReadAsText(j,9);
          m10:=mSheet.ReadAsText(j,10);
          m11:=mSheet.ReadAsText(j,11);
          m12:=mSheet.ReadAsText(j,12);
          m13:=mSheet.ReadAsText(j,13);
          m14:=mSheet.ReadAsText(j,14);
          m15:=mSheet.ReadAsText(j,15);
          m16:=mSheet.ReadAsText(j,16);
          m17:=mSheet.ReadAsText(j,17);
          m18:=mSheet.ReadAsText(j,18);
          m19:=mSheet.ReadAsText(j,19);
          m20:=mSheet.ReadAsText(j,20);
          m21:=mSheet.ReadAsText(j,21);
          m22:=mSheet.ReadAsText(j,22);
          m23:=mSheet.ReadAsText(j,23);
          m24:=mSheet.ReadAsText(j,24);
          m25:=mSheet.ReadAsText(j,25);
          m26:=mSheet.ReadAsText(j,26);
          m27:=mSheet.ReadAsText(j,27);
          m28:=mSheet.ReadAsText(j,28);
          m29:=mSheet.ReadAsText(j,29);
          m30:=mSheet.ReadAsText(j,30);
          m31:=mSheet.ReadAsText(j,31);
          m32:=mSheet.ReadAsText(j,32);
          m33:=mSheet.ReadAsText(j,33);
          m34:=mSheet.ReadAsText(j,34);
          m35:=mSheet.ReadAsText(j,35);
          m36:=mSheet.ReadAsText(j,36);
          m37:=mSheet.ReadAsText(j,37);
          m38:=mSheet.ReadAsText(j,38);
          m39:=mSheet.ReadAsText(j,39);
          m40:=mSheet.ReadAsText(j,40);

          m41:=mSheet.ReadAsText(j,3);

          if (Trim(m0)='')
          or (Trim(m1)='')
          or (Trim(m2)='') //单位编号、税务编号、参保单位名称不能为空
          then
          begin
            Log('数据解析错误，关键字缺失：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          mB:=True;
          //处理c10和c11两个日期值
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          if trim(m10)='' then
            mm10:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m10)>0 then
            begin
              try
                mm10:=strtodate(trim(m10+'-1'),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm10,m1,m2,m3);
              except
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                mB:=mB and False;
              end;
            end
            else
            if Pos('/',m10)>0 then
            begin
              try
                mm10:=strtodate(trim(m10+'/1'),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm10,m1,m2,m3);
              except
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                mB:=mB and False;
              end;
            end
            else
            begin
              try
                mm10:=strtodate(copy(trim(m10),1,4)+'-'+copy(trim(m10),5,2)+'-'+copy(trim(m10),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                mB:=mB and False;
              end;
            end;
          end;

          if trim(m11)='' then
            mm11:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11+'-1'),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm10,m1,m2,m3);
              except
                mm11:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                mB:=mB and False;
              end;
            end
            else
            if Pos('/',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11+'/'),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm10,m1,m2,m3);
              except
                mm11:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                mB:=mB and False;
              end;
            end
            else
            begin
              try
                mm11:=strtodate(copy(trim(m11),1,4)+'-'+copy(trim(m11),5,2)+'-'+copy(trim(m11),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mm11:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                mB:=mB and False;
              end;
            end;
          end;

          //处理C12到c17的浮点数和整数
          if trim(m12) = '' then m12:='0';
          if trim(m13) = '' then m13:='0';
          if trim(m14) = '' then m14:='0';
          if trim(m15) = '' then m15:='0';
          if trim(m16) = '' then m16:='0';
          if trim(m17) = '' then m17:='0';

          try
            mm12:=strtofloat(m12);
          except
            mm12:=0;
            Log('数字解析错误：列12，行'+inttostr(j)+' IN '+mDispFile,True);
            mB:=mB and False;
          end;
          try
            mm13:=strtoint(m13);
          except
            mm13:=0;
            Log('数字解析错误：列13，行'+inttostr(j)+' IN '+mDispFile,True);
            mB:=mB and False;
          end;
          try
            mm14:=strtoint(m14);
          except
            mm14:=0;
            Log('数字解析错误：列14，行'+inttostr(j)+' IN '+mDispFile,True);
            mB:=mB and False;
          end;
          try
            mm15:=strtoint(m15);
          except
            mm15:=0;
            Log('数字解析错误：列15，行'+inttostr(j)+' IN '+mDispFile,True);
            mB:=mB and False;
          end;
          try
            mm16:=strtoint(m16);
          except
            mm16:=0;
            Log('数字解析错误：列16，行'+inttostr(j)+' IN '+mDispFile,True);
            mB:=mB and False;
          end;
          try
            mm17:=strtoint(m17);
          except
            mm17:=0;
            Log('数字解析错误：列17，行'+inttostr(j)+' IN '+mDispFile,True);
            mB:=mB and False;
          end;

          //处理c2参保单位名称中带括号的，截取出来放41
          m41:='';
          m2:=StringReplace(m2,'（','(',[rfReplaceAll]);//去除全角括号
          m2:=StringReplace(m2,'）',')',[rfReplaceAll]);//去除全角括号
          if Pos('(', m2)>0 then
          begin
            //带括号的
            mTmp:=Copy(m2, Pos('(',m2)+1, Length(m2));
            m41:=StringReplace(mTmp,')','',[rfReplaceAll]);
            m2:=Copy(m2,1, Pos('(',m2)-1);
          end;

          //保存到数据库
          if mB=True then
          begin
            Dm.InsertDanwei(SpinEdit1.Value, SpinEdit2.Value,m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,mm10,mm11,mm12,mm13,mm14,mm15,mm16,mm17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41, CheckBox1.Checked);
            mCount:=mCount+1;
            if mCount mod 50 = 0 then
            begin
              Log('已导入'+inttostr(mCount)+'条记录……');
            end;
          end;
        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Zhuxiao(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11:string;
  mm8, mm11:TDate;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteZhuxiao(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow do
        begin
          {
          c0:业务编码
          c1:事项ID
          c2:事项编码
          c3:事项名称
          c4:申请对象类型
          c5:系统类型
          c6:统一社会信用代码
          c7:反馈类型
          c8:经办时间
          c9:办理意见
          c10:办理人
          c11:办理时间
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);
          m9:=mSheet.ReadAsText(j,9);
          m10:=mSheet.ReadAsText(j,10);
          m11:=mSheet.ReadAsText(j,11);

          if (Trim(m0)='')
          or (Trim(m1)='')
          {or (Trim(m2)='') //
          or (Trim(m3)='')}
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          mB:=True;

          if trim(m8)='' then
            mm8:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m8)>0 then
            begin
              try
                mm8:=strtodate(trim(m8),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm8:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m8)>0 then
            begin
              try
                mm8:=strtodate(trim(m8),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm8:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm8:=strtodate(copy(trim(m8),1,4)+'-'+copy(trim(m8),5,2)+'-'+copy(trim(m8),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm8:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;

          if trim(m11)='' then
            mm11:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm11:=strtodate(copy(trim(m11),1,4)+'-'+copy(trim(m11),5,2)+'-'+copy(trim(m11),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;

          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertZhuxiao(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,m4,m5,m6,m7,mm8,m9,m10,mm11, CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;

        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Xuesheng(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15:string;
  mm3, mm9:TDate;
  mm12:integer;
  mm13,mm14,mm15:double;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteXueSheng(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow do
        begin
          {
          c0:社会保障号码
          c1:姓名
          c2:性别
          c3:出生日期
          c4:街道名称
          c5:社区名称
          c6:学校名称
          c7:家庭名称
          c8:缴费人员类别
          c9:缴费日期
          c10:缴款所属起始年月
          c11:缴款所属终止年月
          c12:缴费月数
          c13:个人缴费
          c14:财政补贴
          c15:总金额
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);
          m9:=mSheet.ReadAsText(j,9);
          m10:=mSheet.ReadAsText(j,10);
          m11:=mSheet.ReadAsText(j,11);
          m12:=mSheet.ReadAsText(j,12);
          m13:=mSheet.ReadAsText(j,13);
          m14:=mSheet.ReadAsText(j,14);
          m15:=mSheet.ReadAsText(j,15);

          if (Trim(m0)='')
          or (Trim(m1)='')
          {or (Trim(m2)='') //
          or (Trim(m3)='')}
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          mB:=True;

          if trim(m3)='' then
            mm3:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m3)>0 then
            begin
              try
                mm3:=strtodate(trim(m3),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm3:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m3)>0 then
            begin
              try
                mm3:=strtodate(trim(m3),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm3:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm3:=strtodate(copy(trim(m3),1,4)+'-'+copy(trim(m3),5,2)+'-'+copy(trim(m3),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm3:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;

          if trim(m9)='' then
            mm9:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m9)>0 then
            begin
              try
                mm9:=strtodate(trim(m9),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm9:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m9)>0 then
            begin
              try
                mm9:=strtodate(trim(m9),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm9:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm9:=strtodate(copy(trim(m9),1,4)+'-'+copy(trim(m9),5,2)+'-'+copy(trim(m9),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm9:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;
          try
            mm12:=strtoint(m12);
          except
            mm12:=0;
          end;
          try
            mm13:=strtofloat(m13);
          except
            mm13:=0;
          end;
          try
            mm14:=strtofloat(m14);
          except
            mm14:=0;
          end;
          try
            mm15:=strtofloat(m15);
          except
            mm15:=0;
          end;
          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertXuesheng(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,mm3,m4,m5,m6,m7,m8,mm9,m10,m11,mm12,mm13,mm14,mm15, CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;

        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Zhuanchu(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27:string;
  mm13:TDate;
  mm8:double;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteZhuanchu(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow-1 do
        begin
          {
          c0:凭证编号
          c1:转移方向
          c2:转移办理完结标志
          c3:社会保障号码
          c4:姓名
          c5:身份证号码
          c6:原参保地行政区划
          c7:新就业地行政区划
          c8:个人账户余额
          c9:系统跟踪流水号
          c10:医疗结清日期
          c11:险种类别
          c12:性别
          c13:出生日期
          c14:户籍所在地
          c15:户口性质
          c16:参保地
          c17:参保起始年月
          c18:参保终止年月
          c19:待遇享受起始年月
          c20:待遇享受终止年月
          c21:原参保地社保机构名称
          c22:原参保地社保机构联系电话
          c23:户主姓名
          c24:户主身份证号码
          c25:凭证经办人
          c26:凭证经办时间
          c27:凭证备注
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);
          m9:=mSheet.ReadAsText(j,9);
          m10:=mSheet.ReadAsText(j,10);
          m11:=mSheet.ReadAsText(j,11);
          m12:=mSheet.ReadAsText(j,12);
          m13:=mSheet.ReadAsText(j,13);
          m14:=mSheet.ReadAsText(j,14);
          m15:=mSheet.ReadAsText(j,15);
          m16:=mSheet.ReadAsText(j,16);
          m17:=mSheet.ReadAsText(j,17);
          m18:=mSheet.ReadAsText(j,18);
          m19:=mSheet.ReadAsText(j,19);
          m20:=mSheet.ReadAsText(j,20);
          m21:=mSheet.ReadAsText(j,21);
          m22:=mSheet.ReadAsText(j,22);
          m23:=mSheet.ReadAsText(j,23);
          m24:=mSheet.ReadAsText(j,24);
          m25:=mSheet.ReadAsText(j,25);
          m26:=mSheet.ReadAsText(j,26);
          m27:=mSheet.ReadAsText(j,27);

          if (Trim(m0)='')
          or (Trim(m1)='')
          {or (Trim(m2)='') //
          or (Trim(m3)='')}
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          mB:=True;

          if trim(m13)='' then
            mm13:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m13)>0 then
            begin
              try
                mm13:=strtodate(trim(m13),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm13:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m13)>0 then
            begin
              try
                mm13:=strtodate(trim(m13),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm13:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm13:=strtodate(copy(trim(m13),1,4)+'-'+copy(trim(m13),5,2)+'-'+copy(trim(m13),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm13:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;

          try
            mm8:=strtoint(m8);
          except
            mm8:=0;
          end;
          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertZhuanchu(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,m4,m5,m6,m7,mm8,m9,m10,m11,m12,mm13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27, CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;

        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Bujiao(Sender: TObject);
var
  i,j,k, mSheetCount, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31:string;
  mm9:TDate;
  mm10,mm11,mm12,mm13,mm14,mm15,mm16,mm17,mm18,mm19,mm20,mm21,mm22,mm23,mm24,mm25,mm26,mm27,mm28:double;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteBujiao(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;

      mSheetCount:=mBook.GetWorksheetCount;

      mCount:=0;
      Dm.conn.AutoCommit:=False;
      Dm.connWork.AutoCommit:=False;
      for k:=0 to mSheetCount-1 do
      begin
        mSheet:=mBook.GetWorksheetByIndex(k);
        Log('处理工作表'+mSheet.Name+'……');
        if mSheet.IsEmpty then Continue;
        mLastRow:=mSheet.GetLastRowIndex();
        Log('sheet'+inttostr(k)+' rows='+inttostr(mLastRow),True);
        //Continue;
        if mSheet<>nil then
        begin
          //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
          mLastRow:=mSheet.GetLastRowIndex();
          for j:=1 to mLastRow do
          begin
            {
            c0:社会保障号码
            c1:姓名
            c2:申报类别
            c3:申报发生原因
            c4:险种标志
            c5:起始年月
            c6:终止年月
            c7:自动流水号
            c8:缴费状态
            c9:缴费日期
            c10:总缴费金额
            c11:单位缴费基数
            c12:个人缴费基数
            c13:单位缴费比例
            c14:个人缴费比例
            c15:单位财政代扣比例
            c16:个人财政代扣比例
            c17:单位月缴费金额
            c18:个人月缴费金额
            c19:单位财政月缴费额
            c20:个人财政月缴费额
            c21:单位总缴费金额
            c22:个人总缴费金额
            c23:单位财政总缴费额
            c24:个人财政总缴费额
            c25:个人记账额
            c26:单位记账额
            c27:利息
            c28:滞纳金
            c29:记账标志
            c30:单位编号
            c31:单位名称

            }

            m0:=mSheet.ReadAsText(j,0);
            m1:=mSheet.ReadAsText(j,1);
            m2:=mSheet.ReadAsText(j,2);
            m3:=mSheet.ReadAsText(j,3);
            m4:=mSheet.ReadAsText(j,4);
            m5:=mSheet.ReadAsText(j,5);
            m6:=mSheet.ReadAsText(j,6);
            m7:=mSheet.ReadAsText(j,7);
            m8:=mSheet.ReadAsText(j,8);
            m9:=mSheet.ReadAsText(j,9);
            m10:=Dm.RemovePercent(mSheet.ReadAsText(j,10));
            m11:=Dm.RemovePercent(mSheet.ReadAsText(j,11));
            m12:=Dm.RemovePercent(mSheet.ReadAsText(j,12));
            m13:=Dm.RemovePercent(mSheet.ReadAsText(j,13));
            m14:=Dm.RemovePercent(mSheet.ReadAsText(j,14));
            m15:=Dm.RemovePercent(mSheet.ReadAsText(j,15));
            m16:=Dm.RemovePercent(mSheet.ReadAsText(j,16));
            m17:=Dm.RemovePercent(mSheet.ReadAsText(j,17));
            m18:=Dm.RemovePercent(mSheet.ReadAsText(j,18));
            m19:=Dm.RemovePercent(mSheet.ReadAsText(j,19));
            m20:=Dm.RemovePercent(mSheet.ReadAsText(j,20));
            m21:=Dm.RemovePercent(mSheet.ReadAsText(j,21));
            m22:=Dm.RemovePercent(mSheet.ReadAsText(j,22));
            m23:=Dm.RemovePercent(mSheet.ReadAsText(j,23));
            m24:=Dm.RemovePercent(mSheet.ReadAsText(j,24));
            m25:=Dm.RemovePercent(mSheet.ReadAsText(j,25));
            m26:=Dm.RemovePercent(mSheet.ReadAsText(j,26));
            m27:=Dm.RemovePercent(mSheet.ReadAsText(j,27));
            m28:=Dm.RemovePercent(mSheet.ReadAsText(j,28));
            m29:=mSheet.ReadAsText(j,29);
            m30:=mSheet.ReadAsText(j,30);
            m31:=mSheet.ReadAsText(j,31);

            {Log('m0="'+m0+'"');
            Log('m1="'+m1+'"');
            Log('m2="'+m2+'"');
            Log('m3="'+m3+'"');
            Log('m4="'+m4+'"');
            Log('m5="'+m5+'"');
            Log('m6="'+m6+'"');
            Log('m7="'+m7+'"');
            Log('m8="'+m8+'"');
            Log('m9="'+m9+'"');
            Log('m10="'+m10+'"');
            Log('m11="'+m11+'"');
            Log('m12="'+m12+'"');
            Log('m13="'+m13+'"');
            Log('m14="'+m14+'"');
            Log('m15="'+m15+'"');
            Log('m16="'+m16+'"');
            Log('m17="'+m17+'"');
            Log('m18="'+m18+'"');
            Log('m19="'+m19+'"');
            Log('m20="'+m20+'"');
            Log('m21="'+m21+'"');
            Log('m22="'+m22+'"');
            Log('m23="'+m23+'"');
            Log('m24="'+m24+'"');
            Log('m25="'+m25+'"');
            Log('m26="'+m26+'"');
            Log('m27="'+m27+'"');
            Log('m28="'+m28+'"');
            Log('m29="'+m29+'"');
            Log('m30="'+m30+'"');
            Log('m31="'+m31+'"');

            continue; }

            if (Trim(m0)='')
            or (Trim(m1)='')
            {or (Trim(m2)='') //
            or (Trim(m3)='')}
            then
            begin
              Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              Continue;
            end;
            //保存到数据库，处理c8和c11两个日期值
            FS1 := DefaultFormatSettings;
            FS1.DateSeparator := '-';
            FS1.ShortDateFormat := 'yyyy-mm-dd';
            FS1.ShortTimeFormat := 'hh:mm:ss';

            FS2 := DefaultFormatSettings;
            FS2.DateSeparator := '/';
            FS2.ShortDateFormat := 'yyyymmdd';
            FS2.ShortTimeFormat := 'hh:mm:ss';

            mB:=True;

            if trim(m9)='' then
              mm9:=strtodate('1900-1-1',FS1)
            else
            begin
              if Pos('-',m9)>0 then
              begin
                try
                  mm9:=strtodate(trim(m9),FS1);
                except
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                end;
              end
              else
              if Pos('/',m9)>0 then
              begin
                try
                  mm9:=strtodate(trim(m9),FS2);
                except
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                end;
              end
              else
              begin
                try
                  mm9:=strtodate(copy(trim(m9),1,4)+'-'+copy(trim(m9),5,2)+'-'+copy(trim(m9),7,2),FS1);
                except
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
                end;
              end;
            end;

            try mm10:=strtofloat(m10); except mm10:=0; end;
            try mm11:=strtofloat(m11); except mm11:=0; end;
            try mm12:=strtofloat(m12); except mm12:=0; end;
            try mm13:=strtofloat(m13); except mm13:=0; end;
            try mm14:=strtofloat(m14); except mm14:=0; end;
            try mm15:=strtofloat(m15); except mm15:=0; end;
            try mm16:=strtofloat(m16); except mm16:=0; end;
            try mm17:=strtofloat(m17); except mm17:=0; end;
            try mm18:=strtofloat(m18); except mm18:=0; end;
            try mm19:=strtofloat(m19); except mm19:=0; end;
            try mm20:=strtofloat(m20); except mm20:=0; end;
            try mm21:=strtofloat(m21); except mm21:=0; end;
            try mm22:=strtofloat(m22); except mm22:=0; end;
            try mm23:=strtofloat(m23); except mm23:=0; end;
            try mm24:=strtofloat(m24); except mm24:=0; end;
            try mm25:=strtofloat(m25); except mm25:=0; end;
            try mm26:=strtofloat(m26); except mm26:=0; end;
            try mm27:=strtofloat(m27); except mm27:=0; end;
            try mm28:=strtofloat(m28); except mm28:=0; end;

            //保存数据
            if mB=True then
            begin
              mCount:=mCount+1;
              Dm.InsertBujiao(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,m4,m5,m6,m7,m8,mm9,mm10,mm11,mm12,mm13,mm14,mm15,mm16,mm17,mm18,mm19,mm20,mm21,mm22,mm23,mm24,mm25,mm26,mm27,mm28,m29,m30,m31, CheckBox1.Checked);
            end;
            if mCount mod 50 = 0 then
            begin
              Log('已导入'+inttostr(mCount)+'条记录……');
            end;
          end;
        end;
      end;
      Dm.conn.Commit;
      Dm.conn.AutoCommit:=True;
      Dm.connWork.Commit;
      Dm.connWork.AutoCommit:=True;
      Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
      Log('===========================');
      dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
      dm.UpdateRecordCount(mCount);
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Jumin(sender: TObject);
var
  i,j,k, mSheetCount, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15:string;
  mm3, mm9:TDate;
  mm12:integer;
  mm13,mm14,mm15:double;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    //if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    //begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteJumin(SpinEdit1.Value,SpinEdit2.Value);
    //end
    //else
    //  CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    Log('===========================');
    Log('文件过大时，需要较长时间读取分析，请耐心等候……');
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;

      mSheetCount:=mBook.GetWorksheetCount;

      mCount:=0;
      Dm.conn.AutoCommit:=False;
      Dm.connWork.AutoCommit:=False;
      for k:=0 to mSheetCount-1 do
      begin
        mSheet:=mBook.GetWorksheetByIndex(k);
        Log('处理工作表'+mSheet.Name+'……');
        if mSheet.IsEmpty then Continue;
        mLastRow:=mSheet.GetLastRowIndex();
        Log(inttostr(mLastRow));
        //Continue;
        if mSheet<>nil then
        begin
          //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
          mLastRow:=mSheet.GetLastRowIndex();
          for j:=1 to mLastRow do
          begin
            {
            c0:社会保障号码
            c1:姓名
            c2:性别
            c3:出生日期
            c4:街道名称
            c5:社区名称
            c6:学校名称
            c7:家庭名称
            c8:缴费人员类别
            c9:缴费日期
            c10:费款所属起始年月
            c11:费款所属终止年月
            c12:缴费月数
            c13:个人缴费
            c14:财政补贴
            c15:总金额
            }

            m0:=mSheet.ReadAsText(j,0);
            m1:=mSheet.ReadAsText(j,1);
            m2:=mSheet.ReadAsText(j,2);
            m3:=mSheet.ReadAsText(j,3);
            m3:=mSheet.ReadAsText(j,3);
            m4:=mSheet.ReadAsText(j,4);
            m5:=mSheet.ReadAsText(j,5);
            m6:=mSheet.ReadAsText(j,6);
            m7:=mSheet.ReadAsText(j,7);
            m8:=mSheet.ReadAsText(j,8);
            m9:=mSheet.ReadAsText(j,9);
            m10:=Dm.RemovePercent(mSheet.ReadAsText(j,10));
            m11:=Dm.RemovePercent(mSheet.ReadAsText(j,11));
            m12:=Dm.RemovePercent(mSheet.ReadAsText(j,12));
            m13:=Dm.RemovePercent(mSheet.ReadAsText(j,13));
            m14:=Dm.RemovePercent(mSheet.ReadAsText(j,14));
            m15:=Dm.RemovePercent(mSheet.ReadAsText(j,15));

            if (Trim(m0)='')
            or (Trim(m1)='')
            {or (Trim(m2)='') //
            or (Trim(m3)='')}
            then
            begin
              Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              Continue;
            end;
            //保存到数据库，处理c8和c11两个日期值
            FS1 := DefaultFormatSettings;
            FS1.DateSeparator := '-';
            FS1.ShortDateFormat := 'yyyy-mm-dd';
            FS1.ShortTimeFormat := 'hh:mm:ss';

            FS2 := DefaultFormatSettings;
            FS2.DateSeparator := '/';
            FS2.ShortDateFormat := 'yyyymmdd';
            FS2.ShortTimeFormat := 'hh:mm:ss';

            mB:=True;

            if trim(m3)='' then
              mm3:=strtodate('1900-1-1',FS1)
            else
            begin
              if Pos('-',m3)>0 then
              begin
                try
                  mm3:=strtodate(trim(m3),FS1);
                except
                  mB:=mB and False;
                  mm3:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if Pos('/',m3)>0 then
              begin
                try
                  mm3:=strtodate(trim(m3),FS2);
                except
                  mB:=mB and False;
                  mm3:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if length(m3)=8 then
              begin
                try
                  mm3:=strtodate(copy(trim(m3),1,4)+'-'+copy(trim(m3),5,2)+'-'+copy(trim(m3),7,2),FS1);
                except
                  mB:=mB and False;
                  mm3:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if length(m3)=5 then
              begin
                try
                  mm3:=UnixDateToDateTime(strtoint(m3));
                except
                  mB:=mB and False;
                  mm3:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              begin
                mB:=mB and False;
                mm3:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end;

            if trim(m9)='' then
              mm9:=strtodate('1900-1-1',FS1)
            else
            begin
              if Pos('-',m9)>0 then
              begin
                try
                  mm9:=strtodate(trim(m9),FS1);
                except
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if Pos('/',m9)>0 then
              begin
                try
                  mm9:=strtodate(trim(m9),FS2);
                except
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if length(m9)=8 then
              begin
                try
                  mm9:=strtodate(copy(trim(m9),1,4)+'-'+copy(trim(m9),5,2)+'-'+copy(trim(m9),7,2),FS1);
                except
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if length(m9)=5 then
              begin
                try
                  mm9:=UnixDateToDateTime(strtoint(m9));
                except
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              begin
                  mB:=mB and False;
                  mm9:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end;

            try mm12:=strtoint(m12); except mm12:=0; end;
            try mm13:=strtofloat(m13); except mm13:=0; end;
            try mm14:=strtofloat(m14); except mm14:=0; end;
            try mm15:=strtofloat(m15); except mm15:=0; end;

            //保存数据
            //if mB=True then
            //begin
              mCount:=mCount+1;
              Dm.InsertJumin(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,mm3,m4,m5,m6,m7,m8,mm9,m10,m11,mm12,mm13,mm14,mm15, CheckBox1.Checked);
            //end
            //else
            //begin
            if mB=False then
              Log('错误：表'+inttostr(k)+'行'+inttostr(j),True);
            //end;
            if mCount mod 50 = 0 then
            begin
              Log('已导入'+inttostr(mCount)+'条记录……');
            end;
            //if mCount mod 1000 = 0 then
            //  Dm.conn.Commit;
          end;
        end;
      end;
      Dm.conn.Commit;
      Dm.conn.AutoCommit:=True;
      Dm.connWork.Commit;
      Dm.connWork.AutoCommit:=True;
      Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
      Log('===========================');
      dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
      dm.UpdateRecordCount(mCount);
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Zhigong(sender: TObject);
var
  i,j,k, mSheetCount, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17:string;
  mm5:TDate;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteZhigong(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    Log('===========================');
    Log('文件过大时，需要较长时间读取分析，请耐心等候……');
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;

      mSheetCount:=mBook.GetWorksheetCount;

      mCount:=0;
      Dm.conn.AutoCommit:=False;
      Dm.connWork.AutoCommit:=False;
      for k:=0 to mSheetCount-1 do
      begin
        mSheet:=mBook.GetWorksheetByIndex(k);
        Log('处理工作表'+mSheet.Name+'……');
        if mSheet.IsEmpty then Continue;
        mLastRow:=mSheet.GetLastRowIndex();
        Log(inttostr(mLastRow));
        //Continue;
        if mSheet<>nil then
        begin
          //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
          mLastRow:=mSheet.GetLastRowIndex();
          for j:=1 to mLastRow do
          begin
            {
            c0:职工类别
            c1:单位编号
            c2:参保单位名称
            c3:姓名
            c4:社会保障号码
            c5:出生日期
            c6:性别
            c7:户口性质
            c8:户口所在地
            c9:联系人
            c10:联系人电话
            c11:通讯地址
            c12:用工形式
            c13:个人身份
            c14:隶属关系
            c15:单位状态
            c16:行业类别
            c17:手机号码
            }

            m0:=mSheet.ReadAsText(j,0);
            m1:=mSheet.ReadAsText(j,1);
            m2:=mSheet.ReadAsText(j,2);
            m3:=mSheet.ReadAsText(j,3);
            m3:=mSheet.ReadAsText(j,3);
            m4:=mSheet.ReadAsText(j,4);
            m5:=mSheet.ReadAsText(j,5);
            m6:=mSheet.ReadAsText(j,6);
            m7:=mSheet.ReadAsText(j,7);
            m8:=mSheet.ReadAsText(j,8);
            m9:=mSheet.ReadAsText(j,9);
            m10:=mSheet.ReadAsText(j,10);
            m11:=mSheet.ReadAsText(j,11);
            m12:=mSheet.ReadAsText(j,12);
            m13:=mSheet.ReadAsText(j,13);
            m14:=mSheet.ReadAsText(j,14);
            m15:=mSheet.ReadAsText(j,15);
            m16:=mSheet.ReadAsText(j,16);
            m17:=mSheet.ReadAsText(j,17);

            if (Trim(m0)='')
            or (Trim(m1)='')
            {or (Trim(m2)='') //
            or (Trim(m3)='')}
            then
            begin
              Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              Continue;
            end;
            //保存到数据库，处理c8和c11两个日期值
            FS1 := DefaultFormatSettings;
            FS1.DateSeparator := '-';
            FS1.ShortDateFormat := 'yyyy-mm-dd';
            FS1.ShortTimeFormat := 'hh:mm:ss';

            FS2 := DefaultFormatSettings;
            FS2.DateSeparator := '/';
            FS2.ShortDateFormat := 'yyyymmdd';
            FS2.ShortTimeFormat := 'hh:mm:ss';

            mB:=True;

            if trim(m5)='' then
              mm5:=strtodate('1900-1-1',FS1)
            else
            begin
              if Pos('-',m5)>0 then
              begin
                try
                  mm5:=strtodate(trim(m5),FS1);
                except
                  mB:=mB and False;
                  mm5:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if Pos('/',m5)>0 then
              begin
                try
                  mm5:=strtodate(trim(m5),FS2);
                except
                  mB:=mB and False;
                  mm5:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if length(m5)=8 then
              begin
                try
                  mm5:=strtodate(copy(trim(m5),1,4)+'-'+copy(trim(m5),5,2)+'-'+copy(trim(m5),7,2),FS1);
                except
                  mB:=mB and False;
                  mm5:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              if length(m5)=5 then
              begin
                try
                  mm5:=UnixDateToDateTime(strtoint(m5));
                except
                  mB:=mB and False;
                  mm5:=strtodate('1900-1-1',FS1);
                  Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
                end;
              end
              else
              begin
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end;

            //保存数据
            //if mB=True then
            //begin
              mCount:=mCount+1;
              Dm.InsertZhigong(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,m4,mm5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17, CheckBox1.Checked);
            //end
            //else
            if mB=False then
            begin
              Log('错误：表'+inttostr(k)+'行'+inttostr(j),True);
            end;
            if mCount mod 50 = 0 then
            begin
              Log('已导入'+inttostr(mCount)+'条记录……');
            end;
          end;
        end;
      end;
      Dm.conn.Commit;
      Dm.conn.AutoCommit:=True;
      Dm.connWork.Commit;
      Dm.connWork.AutoCommit:=True;
      Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
      Log('===========================');
      dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
      dm.UpdateRecordCount(mCount);
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Gaoxueya(sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8:string;
  mm7, mm8:TDate;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteGaoxueya(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow do
        begin
          {
          c0:社会保障号码
          c1:姓名
          c2:区县
          c3:街道
          c4:社区
          c5:备案疾病
          c6:备案医院
          c7:备案起始日期
          c8:备案终止日期
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);

          if (Trim(m0)='')
          or (Trim(m1)='')
          {or (Trim(m2)='') //
          or (Trim(m3)='')}
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          mB:=True;

          if trim(m8)='' then
            mm8:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m8)>0 then
            begin
              try
                mm8:=strtodate(trim(m8),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                //mB:=mB and False;
                mm8:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m8)>0 then
            begin
              try
                mm8:=strtodate(trim(m8),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                //mB:=mB and False;
                mm8:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm8:=strtodate(copy(trim(m8),1,4)+'-'+copy(trim(m8),5,2)+'-'+copy(trim(m8),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                //mB:=mB and False;
                mm8:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;

          if trim(m7)='' then
            mm7:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m7)>0 then
            begin
              try
                mm7:=strtodate(trim(m7),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm7:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            if Pos('/',m7)>0 then
            begin
              try
                mm7:=strtodate(trim(m7),FS2);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm7:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end
            else
            begin
              try
                mm7:=strtodate(copy(trim(m7),1,4)+'-'+copy(trim(m7),5,2)+'-'+copy(trim(m7),7,2),FS1);
                //dm.InsertSiwang(SpinEdit1.Value,SpinEdit2.Value,mm0,m1,m2,m3);
              except
                mB:=mB and False;
                mm7:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
              end;
            end;
          end;

          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertGaoxueya(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,m4,m5,m6,mm7,mm8, CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;

        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Manxingbing(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22:string;
  mm10, mm11:TDate;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteManxingbing(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow do
        begin
          {
          c0:备案号
          c1:社会保障号码
          c2:姓名
          c3:性别
          c4:人员状态
          c5:出生年月
          c6:联系电话
          c7:组织类别
          c8:组织编号
          c9:组织名称
          c10:起始日期
          c11:终止日期
          c12:医疗统筹类别明细
          c13:就医类别
          c14:经办人
          c15:备案医院编码
          c16:备案医院名称
          c17:是否允许互联网结算
          c18:治疗方式
          c19:门诊大病备案疾病编码
          c20:门诊大病备案疾病
          c21:门诊大病备案病种
          c22:备注
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);
          m9:=mSheet.ReadAsText(j,9);
          m10:=mSheet.ReadAsText(j,10);
          m11:=mSheet.ReadAsText(j,11);
          m12:=mSheet.ReadAsText(j,12);
          m13:=mSheet.ReadAsText(j,13);
          m14:=mSheet.ReadAsText(j,14);
          m15:=mSheet.ReadAsText(j,15);
          m16:=mSheet.ReadAsText(j,16);
          m17:=mSheet.ReadAsText(j,17);
          m18:=mSheet.ReadAsText(j,18);
          m19:=mSheet.ReadAsText(j,19);
          m20:=mSheet.ReadAsText(j,20);
          m21:=mSheet.ReadAsText(j,21);
          m22:=mSheet.ReadAsText(j,22);

          if (Trim(m0)='')
          or (Trim(m1)='')
          {or (Trim(m2)='') //
          or (Trim(m3)='')}
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          mB:=True;

          {if trim(m5)='' then
              mm5:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m5)>0 then
            begin
              try
                mm5:=strtodate(trim(m5),FS1);
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if Pos('/',m5)>0 then
            begin
              try
                mm5:=strtodate(trim(m5),FS2);
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m5)=8 then
            begin
              try
                mm5:=strtodate(copy(trim(m5),1,4)+'-'+copy(trim(m5),5,2)+'-'+copy(trim(m5),7,2),FS1);
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m5)=5 then
            begin
              try
                mm5:=UnixDateToDateTime(strtoint(m5));
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            begin
              mB:=mB and False;
              mm5:=strtodate('1900-1-1',FS1);
              Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
            end;
          end;
          }

          if trim(m10)='' then
              mm10:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m10)>0 then
            begin
              try
                mm10:=strtodate(trim(m10),FS1);
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if Pos('/',m10)>0 then
            begin
              try
                mm10:=strtodate(trim(m10),FS2);
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m10)=8 then
            begin
              try
                mm10:=strtodate(copy(trim(m10),1,4)+'-'+copy(trim(m10),5,2)+'-'+copy(trim(m10),7,2),FS1);
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m10)=5 then
            begin
              try
                mm10:=UnixDateToDateTime(strtoint(m10));
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            begin
              mB:=mB and False;
              mm10:=strtodate('1900-1-1',FS1);
              Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
            end;
          end;

          if trim(m11)='' then
              mm11:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11),FS1);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if Pos('/',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11),FS2);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m11)=8 then
            begin
              try
                mm11:=strtodate(copy(trim(m11),1,4)+'-'+copy(trim(m11),5,2)+'-'+copy(trim(m11),7,2),FS1);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m11)=5 then
            begin
              try
                mm11:=UnixDateToDateTime(strtoint(m11));
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            begin
              mB:=mB and False;
              mm11:=strtodate('1900-1-1',FS1);
              //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
            end;
          end;

          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertManxingbing(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,mm10,mm11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22, CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;

        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_ZhigongManxingbing(sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24:string;
  mm10, mm11:TDate;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteZhigongManxingbing(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow do
        begin
          {
          c0:备案号
          c1:社会保障号码
          c2:姓名
          c3:性别
          c4:人员状态
          c5:出生年月
          c6:联系电话
          c7:组织类别
          c8:组织编号
          c9:组织名称
          c10:起始日期
          c11:终止日期
          c12:医疗统筹类别明细
          c13:就医类别
          c14:经办人
          c15:备案医院编码
          c16:备案医院名称
          c17:是否允许互联网结算
          c18:治疗方式
          c19:门诊大病备案疾病编码
          c20:门诊大病备案疾病
          c21:门诊大病备案病种
          c22:备注
          c23:住院号
          c24:诊断
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);
          m4:=mSheet.ReadAsText(j,4);
          m5:=mSheet.ReadAsText(j,5);
          m6:=mSheet.ReadAsText(j,6);
          m7:=mSheet.ReadAsText(j,7);
          m8:=mSheet.ReadAsText(j,8);
          m9:=mSheet.ReadAsText(j,9);
          m10:=mSheet.ReadAsText(j,10);
          m11:=mSheet.ReadAsText(j,11);
          m12:=mSheet.ReadAsText(j,12);
          m13:=mSheet.ReadAsText(j,13);
          m14:=mSheet.ReadAsText(j,14);
          m15:=mSheet.ReadAsText(j,15);
          m16:=mSheet.ReadAsText(j,16);
          m17:=mSheet.ReadAsText(j,17);
          m18:=mSheet.ReadAsText(j,18);
          m19:=mSheet.ReadAsText(j,19);
          m20:=mSheet.ReadAsText(j,20);
          m21:=mSheet.ReadAsText(j,21);
          m22:=mSheet.ReadAsText(j,22);
          m23:=mSheet.ReadAsText(j,23);
          m24:=mSheet.ReadAsText(j,24);

          if (Trim(m0)='')
          or (Trim(m1)='')
          {or (Trim(m2)='') //
          or (Trim(m3)='')}
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          FS1 := DefaultFormatSettings;
          FS1.DateSeparator := '-';
          FS1.ShortDateFormat := 'yyyy-mm-dd';
          FS1.ShortTimeFormat := 'hh:mm:ss';

          FS2 := DefaultFormatSettings;
          FS2.DateSeparator := '/';
          FS2.ShortDateFormat := 'yyyymmdd';
          FS2.ShortTimeFormat := 'hh:mm:ss';

          mB:=True;

          {if trim(m5)='' then
              mm5:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m5)>0 then
            begin
              try
                mm5:=strtodate(trim(m5),FS1);
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if Pos('/',m5)>0 then
            begin
              try
                mm5:=strtodate(trim(m5),FS2);
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m5)=8 then
            begin
              try
                mm5:=strtodate(copy(trim(m5),1,4)+'-'+copy(trim(m5),5,2)+'-'+copy(trim(m5),7,2),FS1);
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m5)=5 then
            begin
              try
                mm5:=UnixDateToDateTime(strtoint(m5));
              except
                mB:=mB and False;
                mm5:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            begin
              mB:=mB and False;
              mm5:=strtodate('1900-1-1',FS1);
              Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
            end;
          end;
          }

          if trim(m10)='' then
              mm10:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m10)>0 then
            begin
              try
                mm10:=strtodate(trim(m10),FS1);
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if Pos('/',m10)>0 then
            begin
              try
                mm10:=strtodate(trim(m10),FS2);
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m10)=8 then
            begin
              try
                mm10:=strtodate(copy(trim(m10),1,4)+'-'+copy(trim(m10),5,2)+'-'+copy(trim(m10),7,2),FS1);
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m10)=5 then
            begin
              try
                mm10:=UnixDateToDateTime(strtoint(m10));
              except
                mB:=mB and False;
                mm10:=strtodate('1900-1-1',FS1);
                Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            begin
              mB:=mB and False;
              mm10:=strtodate('1900-1-1',FS1);
              Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
            end;
          end;

          if trim(m11)='' then
              mm11:=strtodate('1900-1-1',FS1)
          else
          begin
            if Pos('-',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11),FS1);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if Pos('/',m11)>0 then
            begin
              try
                mm11:=strtodate(trim(m11),FS2);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m11)=8 then
            begin
              try
                mm11:=strtodate(copy(trim(m11),1,4)+'-'+copy(trim(m11),5,2)+'-'+copy(trim(m11),7,2),FS1);
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            if length(m11)=5 then
            begin
              try
                mm11:=UnixDateToDateTime(strtoint(m11));
              except
                mB:=mB and False;
                mm11:=strtodate('1900-1-1',FS1);
                //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
              end;
            end
            else
            begin
              mB:=mB and False;
              mm11:=strtodate('1900-1-1',FS1);
              //Log('日期解析错误：行'+inttostr(j)+' IN '+mDispFile, True);
            end;
          end;

          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertZhigongManxingbing(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,m4,m5,m6,m7,m8,m9,mm10,mm11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24, CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;

        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_ManbingBianma(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3:string;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteManbingBianma(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=2 to mLastRow do
        begin
          {
          c0:序号
          c1:疾病编码
          c2:疾病名称
          c3:医院级别
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);

          if (Trim(m0)='')
          or (Trim(m1)='')
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          mB:=True;
          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertManbingBianma(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3,CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;
        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_YiyuanBianma(Sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2,m3:string;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    //if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    //begin
      //删除同年同月的划拨数据
      //Log('正在删除已存在的数据');
      //Dm.DeleteYiyuanBianma;
    //end
    //else
    //  CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=1 to mLastRow do
        begin
          {
          c0:序号
          c1:医疗机构名称
          c2:医疗机构编码
          c3:级别
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);
          m3:=mSheet.ReadAsText(j,3);

          if (Trim(m0)='')
          or (Trim(m1)='')
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          mB:=True;
          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertYiyuanBianma(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,m3, CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;
        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

procedure TfrmDaoru_1.Daoru_Zhigongmanxingbingbianma(sender: TObject);
var
  i,j, mLastRow, mCount:integer;
  mFile,mDispFile:string;
  mbook: TsWorkbook;
  mSheet: TsWorksheet;
  mSource: TsWorkbookSource;
  m0,m1,m2:string;
  FS1,FS2: TFormatSettings;
  mB:boolean;
begin
  //开始导入
  SpinEdit1.Enabled:=False;
  SpinEdit2.Enabled:=False;
  BCButton1.Enabled:=False;
  CheckBox1.Enabled:=False;
  ls.Enabled:=False;
  Bar1.Position:=0;
  Memo1.Lines.Clear;
  Memo3.Lines.Clear;
  Application.ProcessMessages;
  //先确认是否确定要删除已经存在的同月数据
  if CheckBox1.Checked then
  begin
    if MessageDlg('确认要删除已经存在的数据吗？',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      //删除同年同月的划拨数据
      Log('正在删除已存在的数据');
      Dm.DeleteZhigongManbingBianma(SpinEdit1.Value, SpinEdit2.Value);
    end
    else
      CheckBox1.Checked:=False;
  end;
  //循环列表中的文件，开始导入数据
  Bar1.Max:=ls.Items.Count;
  for i:=0 to ls.Items.Count-1 do
  begin
    Bar1.Position:=i+1;
    mFile:=ls.Items.Item[i].SubItems.Strings[3];
    mDispFile:=ExtractFilename(mFile);
    //导入EXCEL文件
    Log('===========================');
    Log('正在导入'+mDispFile);
    Log('文件路径：'+ExtractFilepath(mFile));
    mSource:=TsWorkbookSource.Create(Self);
    try
      mSource.AutoDetectFormat:=True;
      mSource.FileName:=mFile;
      mbook:=mSource.Workbook;
      mSheet:=mBook.GetFirstWorksheet;
      if mSheet<>nil then
      begin
        //此处需要根据表格循环读取CELL，通过为空判断是否该行有数据
        mLastRow:=mSheet.GetLastRowIndex();
        mCount:=0;
        Dm.conn.AutoCommit:=False;
        Dm.connWork.AutoCommit:=False;
        for j:=2 to mLastRow do
        begin
          {
          c0:序号
          c1:疾病编码
          c2:疾病名称
          }

          m0:=mSheet.ReadAsText(j,0);
          m1:=mSheet.ReadAsText(j,1);
          m2:=mSheet.ReadAsText(j,2);

          if (Trim(m0)='')
          or (Trim(m1)='')
          then
          begin
            Log('数据解析错误：行'+inttostr(j)+' IN '+mDispFile,True);
            Continue;
          end;
          //保存到数据库，处理c8和c11两个日期值
          mB:=True;
          //保存数据
          if mB=True then
          begin
            mCount:=mCount+1;
            Dm.InsertZhigongManbingBianma(SpinEdit1.Value, SpinEdit2.Value, m0,m1,m2,CheckBox1.Checked);
          end;
          if mCount mod 50 = 0 then
          begin
            Log('已导入'+inttostr(mCount)+'条记录……');
          end;
        end;
        Dm.conn.Commit;
        Dm.conn.AutoCommit:=True;
        Dm.connWork.Commit;
        Dm.connWork.AutoCommit:=True;
        Log('【'+mDispFile+'】共导入'+inttostr(mCount)+'条记录。');
        Log('===========================');
        dm.SaveImportLog(Label1.Caption, SpinEdit1.Value, SpinEdit2.Value, mFile, mDispFile, mCount);
        dm.UpdateRecordCount(mCount);
      end;
    except
      Log('读取文件时发生错误：'+mDispFile,True);
    end;
    mSource.Free;
    Log('本次导入完毕。');
    //保存日志文件
    Memo1.Lines.SaveToFile(ExtractFilepath(Application.ExeName)+'log\import_2_'+Formatdatetime('yyyymmdd-hhmmss',Now)+'.log');
  end;
  SpinEdit1.Enabled:=True;
  SpinEdit2.Enabled:=True;
  BCButton1.Enabled:=True;
  CheckBox1.Enabled:=True;
  ls.Enabled:=True;
end;

end.

